import random
import pandas as pd

# 解决数据输出时列名不对齐的问题
pd.set_option('display.unicode.east_asian_width', True)


def round_to_nearest_five(num):
    return 5 * (num // 5)


def generate_random_number(average):
    # 生成随机数
    random_number = random.gauss(average, 1)
    # 确保结果是整数
    n = int(round(random_number))
    return fix(n)

def fix(n):
    if n > 5:
        return 5
    elif n <= 0:
        return 1
    else:
        return n

if __name__ == '__main__':
    # excel_file = r"D:\100-Project\2024-2025-1学期安排\武昌首义学院\24101308_《通信数据分析技术（企业）》\成绩计算\tmp.xlsx"
    excel_file = r"D:\100-Project\2024-2025-1学期安排\武昌首义学院\24101309_《通信数据运维技术（企业）》\成绩计算\tmp.xlsx"
    df = pd.read_excel(excel_file)

    df['课堂成绩1'] = df['成绩'].apply(generate_random_number)
    df['课堂成绩2'] = df['成绩'].apply(generate_random_number)
    df['课堂成绩3'] = df['成绩'].apply(generate_random_number)
    df['课堂成绩4'] = df['成绩'].apply(generate_random_number)
    df['课堂成绩5'] = df['成绩'].apply(generate_random_number)
    df['课堂成绩6'] = df['成绩'].apply(generate_random_number)
    print(df)
    df.to_excel(excel_file)